"""
作者：元明
日期：2023年11月25日
"""

from flask import *
from pymysql import *

# 声明一个蓝图对象
bookBP = Blueprint(name="book", import_name=__name__)

# 创建数据库连接对象
db_connect = Connect(host="127.0.0.1", port=3306, user="root", password="123123123", database="bms", charset="utf8")


# 首页接口
@bookBP.route("/")
def index():
    return render_template("index.html")


# 处理数据列表的接口
@bookBP.route("/list")
def list():
    # 创建游标对象
    cursor = db_connect.cursor()
    # 编写 SQL 语句
    sql_str = '''select * from book'''
    # 执行 sql
    cursor.execute(sql_str)
    # 获取查询结果
    data = cursor.fetchall()
    # 关闭游标对象
    cursor.close()
    # 定义一个空列表，用来保存返回来的数据
    datas = []
    for item in data:
        book = {}
        book["bid"] = item[0]
        book["name"] = item[1]
        book["price"] = item[2]
        book["summary"] = item[3]
        book["quantity"] = item[4]
        datas.append(book)
    return datas


# 添加数据接口
@bookBP.route("/add", method=["GET", "POST"])
def add():
    if request.method == "GET":
        return render_template("add.html")
    else:
        name = request.values.get("name")
        price = request.values.get("price")
        summary = request.values.get("summary")
        quantity = request.values.get("quantity")
        cursor = db_connect.cursor()
        sql_str = f'''insert into book(name,price,summary,quantity) values('{name}','{price}','{summary}','{quantity}')'''
        cursor.execute(sql_str)
        db_connect.commit()
        cursor.close()
        return redirect("/")


# 修改数据接口
@bookBP.route("/change/<bid>", methods=["GET", "POST"])
def change(bid):
    if request.method == "GET":
        return render_template("change.html")
    else:
        name = request.values.get("name")
        price = request.values.get("price")
        summary = request.values.get("summary")
        quantity = request.values.get("quantity")
        cursor = db_connect.cursor()
        sql_str = f'''update book set name=%s,price=%s,summary=%s,quantity=%s where bid = %s'''
        cursor.execute(sql_str, [name, price, summary, quantity, bid])
        db_connect.commit()
        cursor.close()
        return redirect("/")


# 修改数据时，修改页面回显数据接口
@bookBP.route("/changeData/<bid>")
def changeData(bid):
    cursor = db_connect.cursor()
    sql_str = f'''select * from book where bid = {bid}'''
    cursor.execute(sql_str)
    item = cursor.fetchone()
    book = {}
    book["bid"] = item[0]
    book["name"] = item[1]
    book["price"] = item[2]
    book["summary"] = item[3]
    book["quantity"] = item[4]
    cursor.close()
    return book


# 删除接口
@bookBP.route("/delete/<bid>")
def delete(bid):
    cursor = db_connect.cursor()
    sql_str = f'''datele from book where bid ={bid}'''
    cursor.execute(sql_str)
    db_connect.commit()
    cursor.close()
    return redirect("/")


# 搜素接口，要求可以在 name 和 summary 字段中进行模糊搜素
@bookBP.route("/search")
def search():
    # 将用户提交过来的搜素内容取出来
    word = f'''%{request.values.get("name")}%'''
    cursor = db_connect.cursor()
    sql_str = '''select * from book where name like %s or summary like %s'''
    cursor.execute(sql_str, [word, word])
    data = cursor.fetchall()
    # 关闭游标对象
    cursor.close()
    # 定义一个空列表，用来保存返回的数据
    datas = []
    for item in data:
        book = {}
        book["bid"] = item[0]
        book["name"] = item[1]
        book["price"] = item[2]
        book["summary"] = item[3]
        book["quantity"] = item[4]
        datas.append(book)
    return datas
